﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace DataAccess
{
    public class HocSinh_DA
    {
        QLHocSinhDataContext ql = new QLHocSinhDataContext();
        public List<HOCSINH> LoadData()
        {
            return ql.HOCSINHs.ToList();
        }
        public bool Them(string hoten, string gt, DateTime ngaysinh, string diachi, string email)
        {
            HOCSINH hs = new HOCSINH();
            hs.HOTENHS = hoten;
            hs.GIOITINH = gt;
            hs.NGAYSINH = ngaysinh;
            hs.DIACHI = diachi;
            hs.EMAIL = email;
            try
            {
                ql.HOCSINHs.InsertOnSubmit(hs);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Sua(int ma, string hoten, string gt, DateTime ngaysinh, string diachi, string email)
        {
            HOCSINH hs = ql.HOCSINHs.Where(p => p.MAHS == ma).FirstOrDefault();
            hs.HOTENHS = hoten;
            hs.GIOITINH = gt;
            hs.NGAYSINH = ngaysinh;
            hs.DIACHI = diachi;
            hs.EMAIL = email;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Xoa(int ma)
        {
            HOCSINH hs = ql.HOCSINHs.Where(p => p.MAHS == ma).FirstOrDefault();
            try
            {
                ql.HOCSINHs.DeleteOnSubmit(hs);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public string Laytuoimin()
        {
            return ql.QUYDINHLOPs.Select(p => p.TUOIMIN).FirstOrDefault().ToString();
        }
        public string Laytuoimax()
        {
            return ql.QUYDINHLOPs.Select(p => p.TUOIMAX).FirstOrDefault().ToString();
        }
        public bool QDTuoi(int tuoimin, int tuoimax)
        {
            QUYDINHLOP q = ql.QUYDINHLOPs.Where(p => p.MATHAYDOI == 1).FirstOrDefault();
            q.TUOIMIN = tuoimin;
            q.TUOIMAX = tuoimax;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        //...............Danh muc 2.......................
        public List<Object> Laylop()
        {
            var sql = from k in ql.LOPs
                      select new { k.TENLOP };
            return sql.ToList<Object>();
        }
        public List<Object> LayHS()
        {
            var sql = from k in ql.HOCSINHs
                      where k.LOP == null
                      select new { k.HOTENHS };
            return sql.ToList<Object>();
        }
        public List<Object> Layhet(string ht)
        {
            var sql = from k in ql.HOCSINHs
                      where k.HOTENHS == ht
                      select new { k.NGAYSINH, k.GIOITINH, k.DIACHI, k.EMAIL };
            return sql.ToList<Object>();
        }
        public bool ThemHS(string lop, string hoten, string ngs, string gt, string dc, string email)
        {
            HOCSINHLOP h = new HOCSINHLOP();
            h.LOP = lop;
            h.HOTENHS = hoten;
            h.NGAYSINH = ngs;
            h.GIOITINH = gt;
            h.DIACHI = dc;
            h.EMAIL = email;
            try
            {
                ql.HOCSINHLOPs.InsertOnSubmit(h);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool SuaHS(string lop, string hoten)
        {
            HOCSINHLOP h = ql.HOCSINHLOPs.Where(p => p.HOTENHS == hoten).FirstOrDefault();
            h.LOP = lop;
            //h.NGAYSINH = ngs;
            //h.GIOITINH = gt;
            //h.DIACHI = dc;
            //h.EMAIL = email;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool XoaHS(string hoten)
        {
            HOCSINHLOP h = ql.HOCSINHLOPs.Where(p => p.HOTENHS == hoten).FirstOrDefault();
            try
            {
                ql.HOCSINHLOPs.DeleteOnSubmit(h);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            } 
        }
        public bool Capnhat(string hoten, string lop)
        {
            HOCSINH h = ql.HOCSINHs.Where(p => p.HOTENHS==hoten).FirstOrDefault();
            h.LOP = lop;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(string ht)
        {
            HOCSINH h = ql.HOCSINHs.Where(p => p.HOTENHS == ht).FirstOrDefault();
            h.LOP = null;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public List<HOCSINHLOP> Getdata()
        {
            return ql.HOCSINHLOPs.ToList();
        }
        public string Demlop(string lop)
        {
            var sql = from k in ql.HOCSINHLOPs
                      where k.LOP == lop
                      select new { k.LOP };
            return sql.ToList().Count.ToString();
        }
        public string Laysiso()
        {
            return ql.QUYDINHLOPs.Select(p => p.SISO).FirstOrDefault().ToString();
        }
        // danh sách học sinh.............
        public void ThemTBHK(string hoten, string lop)
        {
            TBHOCKY b = new TBHOCKY();
            b.HOTENHS = hoten;
            b.LOP = lop;
            ql.TBHOCKies.InsertOnSubmit(b);
            ql.SubmitChanges();
        }
        public void XoaTBHK(string hoten, string lop)
        {
            TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == hoten).Where(o => o.LOP == lop).FirstOrDefault();
            ql.TBHOCKies.DeleteOnSubmit(b);
            ql.SubmitChanges();
        }
        public void CapnhatTBHK(string hoten, string lop, float tbhk1, float tbhk2, string hk)
        {
            TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == hoten).Where(o => o.LOP == lop).FirstOrDefault();
            switch (hk)
            {
                case "Học Kỳ I": 
                        b.TBHK1 = tbhk1;
                        ql.SubmitChanges();
                        break;
                case "Học Kỳ II": 
                        b.TBHK2 = tbhk2;
                        ql.SubmitChanges();
                        break;
            }
            //if (hk == "miss")
            //{
            //    TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == hoten).Where(o => o.LOP == lop).FirstOrDefault();
            //    b.HOTENHS = hoten;
            //    b.LOP = lop;
            //    ql.SubmitChanges();
            //}
            //else if (hk == "Học Kỳ I")
            //{
            //    TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == hoten).Where(o => o.LOP == lop).FirstOrDefault();
            //    b.TBHK1 = tbhk1;
            //    ql.SubmitChanges();
            //}
            //else
            //{
            //    TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == hoten).Where(o => o.LOP == lop).FirstOrDefault();
            //    b.TBHK2 = tbhk2;
            //    ql.SubmitChanges();
            //}
        }
        public void capnhatlai(string ten, string lop)
        {
            TBHOCKY b = ql.TBHOCKies.Where(p => p.HOTENHS == ten).FirstOrDefault();
            b.LOP = lop;
            ql.SubmitChanges();
        }

    }
}
